package com.lovo.roomProject.room.dao.impl;

import com.lovo.roomProject.dao.BaseDAO;
import com.lovo.roomProject.pojo.ClasszDO;
import com.lovo.roomProject.pojo.CutPageBean;
import com.lovo.roomProject.pojo.RoomDO;
import com.lovo.roomProject.pojo.StudentDO;
import com.lovo.roomProject.room.dao.RoomDAO;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class RoomDAOImpl extends BaseDAO implements RoomDAO {
    @Override
    public List listRoom() {
        this.getConnection();
        String sql = "select r.*,count(s.s_name) from t_room r left join t_student s on s.s_r_id=r.r_id group by r.r_id";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            rs = statement.executeQuery();
            while (rs.next()) {
                RoomDO roomDO=fillRoom();
                list.add(roomDO);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(rs, statement, con);
        }
        return list;
    }

    @Override
    public List listRoom(int id) {
        this.getConnection();
        String sql="select s.*,c.* from t_room r left join t_student s on s.s_r_id=r.r_id left join t_class c on s.s_c_id=c.c_id where r.r_id=? group by r.r_id,s.s_name";
        List list=new ArrayList();
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,id);
            rs=statement.executeQuery();
            while (rs.next()){
                StudentDO studentDO=new StudentDO();
                ClasszDO classzDO=new ClasszDO();
                studentDO.setName(rs.getString("s.s_name"));
                studentDO.setPhone(rs.getString("s.s_phone"));
                classzDO.setName(rs.getString("c.c_name"));
                studentDO.setClassz(classzDO);
                list.add(studentDO);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List listRoom(String type) {
        this.getConnection();
        String sql = "select r.r_id,r.r_addr,r.r_count" +
                " from t_student s right join t_room r on s.s_r_id=r.r_id" +
                " where r.r_type=? group by r.r_id,r.r_addr,r.r_count" +
                " having count(s.s_id)<r.r_count";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,type);
            rs = statement.executeQuery();
            while (rs.next()) {
                RoomDO roomDO = new RoomDO();
                roomDO.setId(rs.getInt("r_id"));
                roomDO.setAddr(rs.getString("r_addr"));
                roomDO.setCount(rs.getInt("r_count"));
                list.add(roomDO);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(rs, statement, con);
        }
        return list;
    }

    @Override
    public void insert(RoomDO roomDO) {
        this.getConnection();
        String sql="INSERT INTO t_room (r_addr,r_count,r_type) VALUES (?,?,?)";
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,roomDO.getAddr());
            statement.setObject(2,roomDO.getCount());
            statement.setObject(3,roomDO.getType());
            statement.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally{
            close();
        }
    }

    @Override
    public RoomDO fillRoom() throws SQLException {
        RoomDO roomDO = new RoomDO();
        roomDO.setId(rs.getInt("r_id"));
        roomDO.setAddr(rs.getString("r_addr"));
        roomDO.setCount(rs.getInt("r_count"));
        roomDO.setType(rs.getString("r_type"));
        roomDO.setStatus(rs.getString("r_status"));
        roomDO.setInCount(rs.getInt("count(s.s_name)"));
        return roomDO;
    }

    @Override
    public CutPageBean listRoomPage(int current,String addr,String status,String can,String type) {
        this.getConnection();
        String selectSql=" select r.* ,count(s.s_name) ";
        String countSql=" select r.*,count(*) cnt ";
        String fromSql=" from t_student s right join t_room r on s.s_r_id=r.r_id ";
        String groupSql=" group by r.r_id ";
        String whereSql=" where 1=1";
        String havSql=" having 2=2";
        String limitSql=" limit ?,? ";
        if (addr!=null && !addr.equals("")){
            whereSql += " and r_addr like '%"+addr+"%'";
        }
        if (!status.equals("不限")){
            whereSql += " and r_status like '%"+status+"%'";
        }
        if (!can.equals("不限")){
            havSql += " and r_count>count(s.s_name)";
        }
        if (!type.equals("不限")){
            whereSql += " and r_type like '%"+type+"%'";
        }
        CutPageBean cutPageBean=new CutPageBean();
        List list=new ArrayList();
        try {
            statement=con.prepareStatement(selectSql+fromSql+whereSql+groupSql+havSql+limitSql);
            statement.setObject(1,(current-1)*CutPageBean.PAGESIZE);
            statement.setObject(2,CutPageBean.PAGESIZE);
            rs=statement.executeQuery();
            while (rs.next()){
                RoomDO roomDO=fillRoom();
                list.add(roomDO);
            }
            cutPageBean.setList(list);
            //计算总数的SQL
            statement=con.prepareStatement(countSql+fromSql+whereSql+havSql);
            rs=statement.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            close(rs,statement,con);
        }
        return cutPageBean;
    }

    @Override
    public RoomDO getRoomID(int id) {
        this.getConnection();
        String sql="select c.*,s.*,r.* ,count(s.s_name) " +
                "from t_student s left join t_class c on c.c_id=s.s_c_id " +
                "right join t_room r on r.r_id=s.s_r_id where r.r_id=?";
        RoomDO roomDO=new RoomDO();
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,id);
            rs=statement.executeQuery();
            if (rs.next()){
                roomDO.setId(rs.getInt("r_id"));
                roomDO.setAddr(rs.getString("r_addr"));
                roomDO.setCount(rs.getInt("r_count"));
                roomDO.setInCount(rs.getInt("count(s.s_name)"));
                roomDO.setStatus(rs.getString("r_status"));
                roomDO.setType(rs.getString("r_type"));

                StudentDO studentDO=new StudentDO();
                studentDO.setId(rs.getInt("s_id"));
                studentDO.setName(rs.getString("s_name"));
                studentDO.setGender(rs.getString("s_gender"));
                studentDO.setPhone(rs.getString("s_phone"));
                studentDO.setRoom(roomDO);

                ClasszDO classzDO=new ClasszDO();
                classzDO.setId(rs.getInt("c_id"));
                classzDO.setName(rs.getString("c_name"));
                studentDO.setClassz(classzDO);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            close();
        }
        return roomDO;
    }

    @Override
    public void delete(int id) {
        this.getConnection();
        String sql="delete from t_room where r_id=?";
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,id);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            close();
        }
    }
}

